package server;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Properties;

/**
 * Created by IntelliJ IDEA.
 * User: sam
 * Date: 25/06/12
 * Time: 19:44
 * To change this template use File | Settings | File Templates.
 */

public class SearchServlet extends MFHttpServlet {

    String UPLOAD_LOCATION = "/home/sam/mf_server_dump/music_finder_uploads"+File.separator;
    private StringBuilder strResp;
    private int numUploads = 0; //OUCH!!! Only bit of domain/meta data we store at class level... hack
    private Connection connection;
    private Document requestXML;
    private Properties props;

    public SearchServlet(){

    }

    @Override
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        System.out.println("SearchServlet.doPost()"+"\n");

        connection = (Connection) getServletContext().getAttribute("db_connection");
        props = (Properties) getServletContext().getAttribute("properties");
        requestXML = (Document) request.getAttribute("request_xml");

        doSearchRequest(request, response);

        connection = null;
    }

    public void doSearchRequest(HttpServletRequest request, HttpServletResponse response){

        System.out.println("doSearchRequest()");

        /*
                <?xml version="1.0" encoding="ISO-8859-1"?>
                <MFRequest>
                    <Request type='Search' track_name='' artist_name=''/>
                    <Genre name='Rock'/>
                    <Genre name='Drum and bass'/>
                </MFRequest>
               */

        StringBuilder query = new StringBuilder();

        /*
        SELECT mf_tracks.name, mf_tracks.artist, mf_users.name, mf_tracks.uploaded
        FROM mf_tracks, mf_genres, mf_users
        WHERE mf_genres.name = 'Dance' and mf_tracks.genre = mf_genres.id AND mf_tracks.user = mf_users.id;
        SELECT mf_tracks.name, mf_tracks.artist, mf_users.name, mf_tracks.uploaded
        FROM mf_tracks
        JOIN mf_genres
        ON mf_tracks.genre = mf_genres.id
        JOIN
        mf_users
        ON mf_tracks.user = mf_users.id
        WHERE mf_genres.name = 'Dance';
         */

        //query.append("SELECT mf_tracks.name, mf_tracks.artist, mf_users.name, mf_tracks.uploaded\n" +
        // "        FROM mf_tracks, mf_genres, mf_users\n" +
        // "        WHERE mf_genres.name = 'Dance' and mf_tracks.genre = mf_genres.id AND mf_tracks.user = mf_users.id;\n" +

        query.append("SELECT mf_tracks.name, mf_tracks.artist, mf_users.name, mf_tracks.uploaded\n" +
                "        FROM mf_tracks\n" +
                "        JOIN mf_genres\n" +
                "        ON mf_tracks.genre = mf_genres.id\n" +
                "        JOIN\n" +
                "        mf_users\n" +
                "        ON mf_tracks.user = mf_users.id\n" +
                "        WHERE ");

        //query.append("SELECT mf_tracks.name, mf_tracks.artist, mf_users.name, mf_tracks.uploaded ");
        //query.append("FROM mf_tracks, mf_users ");
        //query.append("WHERE mf_tracks.genre IN (SELECT id from mf_genres WHERE ");

        NodeList nList = requestXML.getElementsByTagName("Request");
        Node nNode = nList.item(0);
        Element eElement = (Element) nNode;
        String trackName = eElement.getAttribute("track_name");
        String artistName = eElement.getAttribute("artist_name");

        if(artistName.length() > 0) {
            query.append("mf_tracks.artist LIKE '%"+artistName+"%' ");
        }

        if(trackName.length() > 0) {
            if(artistName.length() > 0) {
                query.append("AND ");
            }
            query.append("mf_tracks.name LIKE '%"+trackName+"%' ");
        }

        if(artistName.length() > 0 || trackName.length() > 0) {
            query.append("AND ");
        }

        //query.append("mf_genres.id = (SELECT id from mf_genres WHERE name='");
        query.append("mf_genres.name = '"); //++"'"");

        nList = requestXML.getElementsByTagName("Genre");
        nNode = nList.item(0);
        eElement = (Element) nNode;

        for (int i = 0; i < nList.getLength(); i++) {
            nNode = nList.item(i);

            if (nNode.getNodeType() == Node.ELEMENT_NODE) {
                eElement = (Element) nNode;
                eElement.normalize();
                String genre = eElement.getAttribute("name");

                query.append(genre+"'");

                if(i < nList.getLength()-1) {
                    query.append(" OR name='");
                }
            }
        }

        //query.append(")) GROUP BY mf_tracks.name;");
        query.append(";");

        System.out.println(query.toString());

        StringBuilder strResp = new StringBuilder();

        try {
            //Class.forName("com.mysql.jdbc.Driver");  //registering the jdbc driver here

            //db_con = DriverManager.getConnection(db_url, db_user, db_pwd);
            Statement db_st = connection.createStatement();
            ResultSet db_rs = db_st.executeQuery(query.toString());

            strResp.append("<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\n");
            strResp.append("<MFResponse>\n");
            strResp.append("<Response type='Search'>\n");

            while (db_rs.next()) {

                String track = db_rs.getString(1);
                String artist = db_rs.getString(2);
                String contributor = db_rs.getString(3);
                String contributed = db_rs.getString(4);

                SimpleDateFormat sqlTimestamp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

                java.util.Date javaDate = sqlTimestamp.parse(contributed);  // convert String "2012-04-28 21:34:20" to Date Sat Apr 28 21:34:20 BST 2012

                //System.out.println("javaDate "+javaDate);

                SimpleDateFormat clientTimestamp = new SimpleDateFormat("MM/dd/yyyy");

                String strJavaDate = clientTimestamp.format(javaDate);  // convert 2012-04-28 21:34:20 to 04/28/2012

                //System.out.println("strJavaDate "+strJavaDate);

                //java.sql.Timestamp
                //java.sql.Date sqlDate = new java.sql.Date();
                //java.util.Date javaDate = new java.util.Date();
                //java.util.Date date = formatter.parse(contributed);

                strResp.append("<Track name='"+track+"' artist='"+artist+"' contributor='"+contributor+"' length='"+"0.00"+"' type='"+"mp3"+"' uploaded='"+strJavaDate+"'/>\n");
            }

            strResp.append("</Response>\n");
            strResp.append("</MFResponse>\n");
        }
        /*catch(ClassNotFoundException e1) {

            System.out.println(e1.getMessage());
            e1.printStackTrace();
        }*/
        catch (SQLException e2) {

            System.out.println(e2.getMessage());
            e2.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }

        write(response,strResp);
    }
}
